using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using MESData.Infrastucture;
using MESData.Infrastucture.Interface;
using ConsoleApi.Models;
using MESBaseis.Domain.XZY;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Logging;
using System.Data.Common;

namespace ConsoleApi.Services.Console
{
    /// <summary>
    /// 查询优化服务 - 提供EF Core查询性能优化功能
    /// </summary>
    public class QueryOptimizationService
    {
        private readonly MyDbcontext _context;
        private readonly IMemoryCache _cache;
        private readonly ILogger<QueryOptimizationService> _logger;

        // 编译查询缓存 - 避免重复编译查询表达式
        private static readonly Func<MyDbcontext, int, Task<AnnouncementModel>> GetAnnouncementByIdCompiled =
            EF.CompileAsyncQuery((MyDbcontext context, int id) =>
                context.Announcements
                    .AsNoTracking()
                    .FirstOrDefault(x => x.Id == id && !x.IsDeleted));

        private static readonly Func<MyDbcontext, int, Task<PurchaseOrderModel>> GetPurchaseOrderByIdCompiled =
            EF.CompileAsyncQuery((MyDbcontext context, int id) =>
                context.PurchaseOrders
                    .AsNoTracking()
                    .FirstOrDefault(x => x.Id == id && !x.IsDeleted));

        private static readonly Func<MyDbcontext, int, Task<ProductionOrderModel>> GetProductionOrderByIdCompiled =
            EF.CompileAsyncQuery((MyDbcontext context, int id) =>
                context.ProductionOrders
                    .AsNoTracking()
                    .FirstOrDefault(x => x.Id == id && !x.IsDeleted));

        private static readonly Func<MyDbcontext, int, Task<ProductionSalesOrderModel>> GetProductionSalesOrderByIdCompiled =
            EF.CompileAsyncQuery((MyDbcontext context, int id) =>
                context.ProductionSalesOrders
                    .AsNoTracking()
                    .FirstOrDefault(x => x.Id == id && !x.IsDeleted));

        private static readonly Func<MyDbcontext, int, Task<ProcessModel>> GetProcessByIdCompiled =
            EF.CompileAsyncQuery((MyDbcontext context, int id) =>
                context.Processes
                    .AsNoTracking()
                    .FirstOrDefault(x => x.Id == id && !x.IsDeleted));

        public QueryOptimizationService(MyDbcontext context, IMemoryCache cache, ILogger<QueryOptimizationService> logger)
        {
            _context = context;
            _cache = cache;
            _logger = logger;
        }

        /// <summary>
        /// 获取通知公告（使用编译查询）
        /// </summary>
        public async Task<AnnouncementModel> GetAnnouncementByIdAsync(int id)
        {
            return await GetAnnouncementByIdCompiled(_context, id);
        }

        /// <summary>
        /// 获取采购订单（使用编译查询）
        /// </summary>
        public async Task<PurchaseOrderModel> GetPurchaseOrderByIdAsync(int id)
        {
            return await GetPurchaseOrderByIdCompiled(_context, id);
        }

        /// <summary>
        /// 获取生产工单（使用编译查询）
        /// </summary>
        public async Task<ProductionOrderModel> GetProductionOrderByIdAsync(int id)
        {
            return await GetProductionOrderByIdCompiled(_context, id);
        }

        /// <summary>
        /// 获取销售订单（使用编译查询）
        /// </summary>
        public async Task<ProductionSalesOrderModel> GetProductionSalesOrderByIdAsync(int id)
        {
            return await GetProductionSalesOrderByIdCompiled(_context, id);
        }

        /// <summary>
        /// 获取工序（使用编译查询）
        /// </summary>
        public async Task<ProcessModel> GetProcessByIdAsync(int id)
        {
            return await GetProcessByIdCompiled(_context, id);
        }

        /// <summary>
        /// 批量获取实体（避免N+1查询）
        /// </summary>
        public async Task<List<T>> GetEntitiesByIdsAsync<T>(IEnumerable<int> ids) where T : class
        {
            if (!ids.Any()) return new List<T>();

            var cacheKey = $"entities_{typeof(T).Name}_{string.Join("_", ids.OrderBy(x => x))}";
            
            if (_cache.TryGetValue(cacheKey, out List<T> cachedResult))
            {
                return cachedResult;
            }

            var result = await _context.Set<T>()
                .AsNoTracking()
                .Where(e => EF.Property<int>(e, "Id") == ids.First() || ids.Contains(EF.Property<int>(e, "Id")))
                .ToListAsync();

            // 缓存结果5分钟
            _cache.Set(cacheKey, result, TimeSpan.FromMinutes(5));
            
            return result;
        }

        /// <summary>
        /// 优化分页查询 - 使用窗口函数提高性能
        /// </summary>
        public async Task<(List<T> Items, int TotalCount)> GetPagedEntitiesAsync<T>(
            IQueryable<T> queryable, 
            int pageIndex, 
            int pageSize) where T : class
        {
            // 使用窗口函数优化分页查询
            var totalCount = await queryable.CountAsync();
            
            var items = await queryable
                .AsNoTracking()
                .Skip((pageIndex - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();

            return (items, totalCount);
        }

        /// <summary>
        /// 批量更新实体（减少数据库往返）
        /// </summary>
        public async Task<int> BatchUpdateAsync<T>(IEnumerable<T> entities) where T : class
        {
            if (!entities.Any()) return 0;

            // 禁用自动变更检测以提高批量操作性能
            _context.ChangeTracker.AutoDetectChangesEnabled = false;
            
            try
            {
                _context.Set<T>().UpdateRange(entities);
                var result = await _context.SaveChangesAsync();
                
                // 手动同步变更
                _context.ChangeTracker.DetectChanges();
                
                return result;
            }
            finally
            {
                _context.ChangeTracker.AutoDetectChangesEnabled = true;
            }
        }

        /// <summary>
        /// 批量创建实体（减少数据库往返）
        /// </summary>
        public async Task<int> BatchCreateAsync<T>(IEnumerable<T> entities) where T : class
        {
            if (!entities.Any()) return 0;

            // 禁用自动变更检测以提高批量操作性能
            _context.ChangeTracker.AutoDetectChangesEnabled = false;
            
            try
            {
                _context.Set<T>().AddRange(entities);
                var result = await _context.SaveChangesAsync();
                
                // 手动同步变更
                _context.ChangeTracker.DetectChanges();
                
                return result;
            }
            finally
            {
                _context.ChangeTracker.AutoDetectChangesEnabled = true;
            }
        }

        /// <summary>
        /// 使用原始SQL查询提高性能（适用于复杂查询）
        /// </summary>
        public async Task<List<T>> ExecuteRawSqlAsync<T>(string sql, params object[] parameters) where T : class
        {
            return await _context.Set<T>().FromSqlRaw(sql, parameters).AsNoTracking().ToListAsync();
        }

        /// <summary>
        /// 获取图表数据的优化查询
        /// </summary>
        public async Task<List<ChartDataPoint>> GetChartDataOptimizedAsync<T>(
            DateTime startDate, 
            DateTime endDate, 
            string timeRange) where T : class
        {
            var cacheKey = $"chartdata_{typeof(T).Name}_{startDate:yyyyMMdd}_{endDate:yyyyMMdd}_{timeRange}";
            
            if (_cache.TryGetValue(cacheKey, out List<ChartDataPoint> cachedResult))
            {
                return cachedResult;
            }

            try
            {
                // 使用LINQ查询，更可靠
                var query = _context.Set<T>()
                    .AsNoTracking()
                    .Where(x => EF.Property<DateTime>(x, "CreateTime") >= startDate 
                               && EF.Property<DateTime>(x, "CreateTime") <= endDate
                               && !EF.Property<bool>(x, "IsDeleted"));

                List<ChartDataPoint> result;

                switch (timeRange)
                {
                    case "year":
                        // 按月分组
                        result = await query
                            .GroupBy(x => new { 
                                Year = EF.Property<DateTime>(x, "CreateTime").Year, 
                                Month = EF.Property<DateTime>(x, "CreateTime").Month 
                            })
                            .Select(g => new ChartDataPoint
                            {
                                Year = g.Key.Year,
                                Month = g.Key.Month,
                                Date = new DateTime(g.Key.Year, g.Key.Month, 1), // 用于排序和显示
                                Count = g.Count(),
                                TotalAmount = g.Sum(x => EF.Property<decimal>(x, "TotalAmount"))
                            })
                            .OrderBy(x => x.Year)
                            .ThenBy(x => x.Month)
                            .ToListAsync();
                        
                        
                        break;

                    case "month":
                        // 按天分组
                        result = await query
                            .GroupBy(x => EF.Property<DateTime>(x, "CreateTime").Date)
                            .Select(g => new ChartDataPoint
                            {
                                Date = g.Key,
                                Count = g.Count(),
                                TotalAmount = g.Sum(x => EF.Property<decimal>(x, "TotalAmount"))
                            })
                            .OrderBy(x => x.Date)
                            .ToListAsync();
                        break;

                    case "week":
                    default:
                        // 按天分组
                        result = await query
                            .GroupBy(x => EF.Property<DateTime>(x, "CreateTime").Date)
                            .Select(g => new ChartDataPoint
                            {
                                Date = g.Key,
                                Count = g.Count(),
                                TotalAmount = g.Sum(x => EF.Property<decimal>(x, "TotalAmount"))
                            })
                            .OrderBy(x => x.Date)
                            .ToListAsync();
                        break;
                }

                _logger.LogInformation($"图表查询结果数量: {result.Count}, 类型: {typeof(T).Name}, 时间范围: {timeRange}");

                // 缓存结果10分钟
                _cache.Set(cacheKey, result, TimeSpan.FromMinutes(10));
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"获取图表数据失败: {ex.Message}");
                return new List<ChartDataPoint>();
            }
        }



        /// <summary>
        /// 图表数据点
        /// </summary>
        public class ChartDataPoint
        {
            public DateTime? Date { get; set; }
            public int? Year { get; set; }
            public int? Month { get; set; }
            public int Count { get; set; }
            public decimal TotalAmount { get; set; }
        }
    }
} 